#用于同步用户状态

@rds = @rds_tt

load 'wechat_api.rb'
load 'rds_api.rb'
load 'tutem_utils.rb'

def subscription_summary

    mp_openids = get_mp_openids
    mp_openids_str = mp_openids.join("','")
    puts "mp_openids 当前实时关注公众号的用户: #{mp_openids.size}"

    sql = "select uid, openid, subscribe from tutem_mall.eb_wechat_user where openid is not null and openid != ''"#in ('#{mp_openids_str}') and subscribe = 1";
    users = {}
    db_openids = []
    subscribed_openids = []

    (queryRds sql).each do |q|
        users.store q['openid'],q['uid']
        db_openids += [q['openid']]
        subscribed_openids += [q['openid']] if q['subscribe'] == 1
    end
    puts "db_openids 数据库里登记过openid的用户: #{db_openids.size}"
    puts "subscribed_openids 数据库里登记过openid且标记为已关注公众号的用户: #{subscribed_openids.size}"

    not_in_mp = db_openids - mp_openids
    puts "not_in_mp 目前没有关注公号的在数据库中登记过openid的用户: #{not_in_mp.size}"

    not_in_db = mp_openids - db_openids
    puts "not_in_db 当前关注了公众号但未在数据库中登记openid的用户: #{not_in_db.size}"

    #ap (subscribed_openids - mp_openids)
    #ap (mp_openids - subscribed_openids)

end

def openids_not_in_db

    mp_openids = get_mp_openids
    mp_openids_str = mp_openids.join("','")

    sql = "select uid, openid, subscribe from tutem_mall.eb_wechat_user where openid is not null and openid != ''"#in ('#{mp_openids_str}') and subscribe = 1";
    users = {}
    db_openids = []

    (queryRds sql).each do |q|
        users.store q['openid'],q['uid']
        db_openids += [q['openid']]
    end

    not_in_db = mp_openids - db_openids

    return not_in_db

end

def sync_subscription reset = false

    queryRds "update tutem_mall.eb_wechat_user set subscribe = 0 where 1=1" if reset

    openids = get_mp_openids
    openids_str = openids.join("','")

    queryRds "update tutem_mall.eb_wechat_user set subscribe = 0 where openid not in ('#{openids_str}')"
    queryRds "update tutem_mall.eb_wechat_user set subscribe = 1 where openid in ('#{openids_str}')"

end

#最近多少天曾经下过单的客户
def get_recent_uid days

end

#一个月没有下过订单的openid
def get_sleeping_list

    crmeb_users = []
    res = queryRds "select openid from tutem_mall.eb_wechat_user"
    res.each do |r|
        crmeb_users += r['openid']
    end
    return []

end

#发现居然有些新用户在打开event_20220204，对数据做检查
def check_data

    openids = ['oqlyX6Qqk7bTTBhqDZcR7_2ZB9qg','oqlyX6eAZkOPlEoASUWKjVnqWMMg','oqlyX6Z8mQKwaJbkcUgMHEFyND38','oqlyX6XOHAWPlL3-jEyxpKXkRW2c','oqlyX6bwV0bCW2yovLnkuCyCgk_s','oqlyX6UODP6JAo8Jpy58ltDV8CB8','oqlyX6YUUVPnwSduOSN8NbmXBiCA','oqlyX6WEAXYlPvsfs12bVxMKhgxc','oqlyX6ck4km_AtB9v3Fa-R6Tqi6E','oqlyX6bhP7m_nYKL1cAYnTS7z0nU','oqlyX6dYaslVyPyVOQgY1PRggbX0','oqlyX6S0YrP3HbwrQYigAPhgGlxg','oqlyX6Rk797YHCyXmPdFW88DbyCg','oqlyX6QPvsf-GL0EAG91_Eg9tPmk','oqlyX6XJUSGUn4L9RfjAf9coCsgc','oqlyX6XCNgJUuJ2Abx6l-EbWKoaI','oqlyX6aynxKr2wkSZy_3ht_pyZQs','oqlyX6dhLasdwnZI3nM5BEFi6EAw','oqlyX6cYX-pjZZ3hyxVslZBdweAo','oqlyX6XlkQfQlWf1tRfKhDSrivSc','oqlyX6eX_Vyt84xH7Nf2NFxf7yrE','oqlyX6YxoVuoUy9X4PqNyk3ZtoDk','oqlyX6TDhDEwmjGwBL42KbWPOKJc','oqlyX6Vk-d7205K0dUA16zc7OPcA','oqlyX6Yu7L4lMn32jj4Oo-XlGeNg','oqlyX6ctmkmAIncu70mZocDZPRvo','oqlyX6Wrtksmf7pOmHrV9p2dydQE','oqlyX6QNsd7DtSM6DKO4S4fA7mM4','oqlyX6bGk1Sbr6mO45z3hQ4nK-ew','oqlyX6U7PpvqqZkynI7KtCU4wLO4','oqlyX6YwWamRkEuVp7WVOKhfrxho','oqlyX6X9AciL2Q4L5t3fTyBYD3i0','oqlyX6a_31rPIfacUKO0v2-IKNFk','oqlyX6RuGyH9s_1TWq6KWJUKuZdQ','oqlyX6b3oPza_O0oPNScMPNMGrfM','oqlyX6ZJBaNlz2SGFKbIfjc9wHGU','oqlyX6fXrDUo8EJxkIpFKbtxE5oc','oqlyX6ZC9BKWwWb47UmShJdsJl3c','oqlyX6bNFTgRElQiG3PoxthlTTAc','oqlyX6YJitAeO9xFXLjbf-80Np_M','oqlyX6VeLx6ExikHU0WE7a04XyJg','oqlyX6ZgstNN929MwUj4EGAO_vys','oqlyX6XOeid2SE21OTkjbPqoY4r0','oqlyX6b92XJbEr3SSiTWL5Pf3Hww','oqlyX6em0oWxvl77VDXZrMzKUC54','oqlyX6V6y93gMki3FStIsOxdrhSc','oqlyX6dbYjE5_NtXWpVbx6pFUD2c','oqlyX6VImKzMcf3-lxVqrsXUkddg','oqlyX6XWp3NeGgVQWg7zIySUt9H8','oqlyX6TTAZ7WwPKNgfkpOdIJccqM','oqlyX6QuY_0qG6S1iN6uquvwTlg4','oqlyX6Tz_NCCU0k-5O47oAA5hhYI','oqlyX6duqRi8SLpO8nw75FOSv-Zg','oqlyX6YpMqZ_6kC3i_VReDH2vAaM','oqlyX6elQgF_gnnkjzKSqCxxytiw','oqlyX6RZ-oIdhe6WFdqhy0zuFGTM','oqlyX6YAB1mC_JmK3LdYbL9A80Lc','oqlyX6Q5OrYxCY58YU8h7wZ4GikE','oqlyX6QjLyUZJoNEQsEo2-hOPTcA','oqlyX6UWNNtqiVmhPs3Sk4wQ2Jsk','oqlyX6YKUbAGJTWD7GxhrmtL8vwc','oqlyX6e5Nv56DnydBOuVgjtnf2zw','oqlyX6YGFJq3LqbNvgRe47DRiUOU','oqlyX6Xu_RUEV_X4QSQheFOO4900','oqlyX6QyVHBsWfH8YLl35o-rDa44','oqlyX6WBarqM-ubxEmCwbdrqaJsg','oqlyX6fJxNBp4yD3uKg5TLbGPU_o','oqlyX6fH55jL3LHt4YFmcTqm_MVM','oqlyX6dz1wxySln4Wi5nWCTLhQko','oqlyX6ZZrm_Op0eoJ-o-TVO0v-rA','oqlyX6Xkt5YNAB0X5L0IsGtDnoKA','oqlyX6UZmMQyUYlXocK-mTKmBHmw','oqlyX6cCXW4BOQepb3XC4CP-8HPE','oqlyX6eS39PwCLpbmYY2Vrl3ACAw','oqlyX6Y00TfIPbn0JlGuUZulpbeg','oqlyX6QcJiFtDs-1QZ1NMVAVikXk','oqlyX6cFslBpVdxmZVDC4WriimXY','oqlyX6ZQFESOU_k9TEu0Nsvb5Ft0','oqlyX6YjnJpJzP3TqeoYsqtFPOGE','oqlyX6bMufx6pNjrWViWxgDyqXHk','oqlyX6Yb2a0a8tQQR3V0y4FDq3ko','oqlyX6bkNnCVWxgj_AfBe2-mdcUI','oqlyX6XInTyWeIc3rPDnIwbfqsuA','oqlyX6Ta0hjboA6MEjxFBlnyLETk','oqlyX6VICE4x0if8NxDmmnw0CuIM','oqlyX6S9a0XbPPmCTydd9tc4Q7MQ','oqlyX6XBQ1ydOKprSuIAHlS-TGrg','oqlyX6ap8MgYMJzncVl6puwvDihw','oqlyX6XJP2Ao6mDWVI24e0J9Tyrs','oqlyX6YC_90CZ_yCB612YC1nKkXk','oqlyX6SMQuXJdmcnrKRLWcbci4XE','oqlyX6f7COP3aFj5KvHp1D6cjeDs','oqlyX6ZaHmlembntMkSd0fJoLvGw','oqlyX6RIczK6dLcexpcEryQnEeDQ','oqlyX6RtjJf_an-3QHsUhRg_uhu0','oqlyX6dBUfbs02QrQwuNYyxLOvdk','oqlyX6RA8jZm6otzj81fzyohmXZA','oqlyX6ZmB-K97l-eWFk8J-1GYcSE','oqlyX6emAxUT5D6XRwPmxg0U93fo','oqlyX6To9aT_ql-JX1C2WBGIe09g','oqlyX6fUZZnscTybntZ1gQski2_s']

    return openids

end

#目标是把所有30天内没有下过单，以及下过订单却没有关注公众号的用户，
#只要在公众号上有关注，就推送一条积分变动消息加代金券领取给她
#在公众号上没有关注的客服，就推送手机短信信给他们

def check_followup_in_db

    mp_openids = get_mp_openids
    idx = 0
    puts "[idx]  uid uniond openid ropenid subscribed"
    no_weappid = {}
    no_unionid = {}
    without_both = {}
    not_subscribed = {}
    mp_openids.each do |openid|
        sql = "select uid, unionid, openid, routine_openid, subscribe from tutem_mall.eb_wechat_user where openid = '#{openid}'"
        (queryRds(sql)).each do |q|
            idx += 1
            uid = q['uid']
            u = q['unionid'].to_s
            r = q['routine_openid'].to_s
            no_unionid.store openid, uid if u.empty?
            no_weappid.store openid, uid if r.empty?
            without_both.store openid, uid if u.empty? && r.empty?
            if u.empty? || r.empty?
                lost_unionid = '---'
                dup_uid = '---'
                if u.empty?
                    mui = get_mp_user_info openid
                    lost_unionid = mui['unionid']
                    sql2 = "select uid from tutem_mall.eb_wechat_user where unionid = '#{lost_unionid}'"
                    (queryRds(sql2)).each do |l|
                        dup_uid = l['uid'].to_s
                    end
                end
                puts "[#{sprintf('%03d',idx)}] uid: #{sprintf('%04d',uid)} openid: #{openid} unionid: #{u} routine_openid: #{r} dup_uid: #{dup_uid} lost_unionid: #{lost_unionid}"
            end
        end
    end

    puts "缺unionid #{no_unionid.size}"
    puts "缺weappid #{no_weappid.size}"
    puts "未标记关注 #{not_subscribed.size}"
    puts "两个都缺 #{without_both.size}"

    #对于缺unionid的,我们可以通过openid到mp接口中查询unionid,查询是否存在其他用户处，如果存在，则合并用户，如果不存在，则更新unionid

end

#fix lost unionid
def fix_lost_unionids

    mp_openids = get_mp_openids
    idx = 0
    no_unionid = {}
    sqls = []
    mp_openids.each do |openid|
        sql = "select uid, unionid, openid, routine_openid, subscribe from tutem_mall.eb_wechat_user where openid = '#{openid}'"
        (queryRds(sql)).each do |q|
            idx += 1
            uid = q['uid']
            u = q['unionid'].to_s
            if u.empty?

                no_unionid.store openid, uid
                dup_uid = 0
                mui = get_mp_user_info openid
                lost_unionid = mui['unionid']
                sql2 = "select uid from tutem_mall.eb_wechat_user where unionid = '#{lost_unionid}'"
                (queryRds(sql2)).each do |l|
                    dup_uid = l['uid']
                end

                #对于缺unionid的,我们可以通过openid到mp接口中查询unionid,查询是否存在其他用户处，如果存在，则合并用户，如果不存在，则更新unionid
                if dup_uid == 0
                    #puts "[#{sprintf('%03d',idx)}] uid: #{uid} openid: #{openid} unionid: #{u} dup_uid: #{dup_uid} lost_unionid: #{lost_unionid}"
                    sqls += ["update tutem_mall.eb_wechat_user set unionid = '#{lost_unionid}' where openid = '#{openid}';"]
                else
                    puts "***[#{sprintf('%03d',idx)}] uid: #{uid} openid: #{openid} unionid: #{u} dup_uid: #{dup_uid} lost_unionid #请手工校对并迁移积分等"
                    ##把配对的两个uid代入下面
                    #select uid, unionid, openid, routine_openid from eb_wechat_user where uid in (816, 1320);
                    #select uid, integral, now_money from eb_user where uid in (816, 1320);
                    #select id, uid, title, number, balance, from_unixtime(add_time) as created_at from eb_user_bill where uid in (816, 1320);
                end

            end
        end
    end

    puts "total no unionid: #{no_unionid.size}"
    #commitTrans sqls

end

def record_wework_external_contacts

    contacts = get_wework_external_list

    usqls = [] #先尝试更新已有记录
    isqls = [] #再尝试插入不存在就
    csqls = [] #更新其它关联记录的

    puts "recording contacts"
    contacts.each do |contact|

        print '.'

        now = Time.now.to_i
        json = contact.to_json

        external_userid = contact['external_contact']['external_userid']       #primary key, 可能是id，也可能是几个键值的联合
        name = contact['external_contact']['name']       #primary key, 可能是id，也可能是几个键值的联合
        unionid = contact['external_contact']['unionid']       #primary key, 可能是id，也可能是几个键值的联合
        type = contact['external_contact']['type']       #数值
        avatar = contact['external_contact']['avatar'].to_s  #常规字符串类需要转义处理
        gender = contact['external_contact']['gender']       #数值

        follow_info_userid = contact['follow_info']['userid']       #primary key, 可能是id，也可能是几个键值的联合
        follow_info_remark = contact['follow_info']['remark'].to_s  #常规字符串类需要转义处理
        follow_info_description = contact['follow_info']['description'].to_s  #常规字符串类需要转义处理
        follow_info_createtime = contact['follow_info']['createtime']       #数值
        follow_info_tagid = contact['follow_info']['tagid'].to_s  #常规字符串类需要转义处理
        follow_info_remark_mobiles = contact['follow_info']['remark_mobiles'].to_s  #常规字符串类需要转义处理
        follow_info_add_way = contact['follow_info']['add_way']       #数值

        usql = "update tutem_mall.eb_wework_external_contact set 
        external_userid = '#{external_userid}',
        name = '#{@rds.escape name}',
        avatar = '#{@rds.escape avatar}',
        gender = #{gender},
        follow_info_userid = '#{follow_info_userid}',
        follow_info_remark = '#{@rds.escape follow_info_remark}',
        follow_info_description = '#{@rds.escape follow_info_description}',
        follow_info_createtime = #{follow_info_createtime},
        follow_info_tagid = '#{@rds.escape follow_info_tagid}',
        follow_info_remark_mobiles = '#{@rds.escape follow_info_remark_mobiles}',
        follow_info_add_way = #{follow_info_add_way},
        json = '#{@rds.escape json}',
        add_time = #{now},
        update_time = #{now}
      where external_userid = '#{external_userid}';"

        isql = "insert into tutem_mall.eb_wework_external_contact( 
        external_userid, unionid, name, avatar, gender,
        follow_info_userid, follow_info_remark, follow_info_description,
        follow_info_createtime, follow_info_tagid, follow_info_remark_mobiles, follow_info_add_way,
        json, add_time, update_time ) select
        '#{external_userid}', '#{unionid}', '#{@rds.escape name}', '#{@rds.escape avatar}', #{gender},
        '#{follow_info_userid}', '#{@rds.escape follow_info_remark}', '#{@rds.escape follow_info_description}',
        #{follow_info_createtime},
        '#{@rds.escape follow_info_tagid}',
        '#{@rds.escape follow_info_remark_mobiles}',#{follow_info_add_way},
        '#{@rds.escape json}', #{now}, #{now}
      from dual
      where not exists ( select id from tutem_mall.eb_wework_external_contact where external_userid = '#{external_userid}');"

        csql = "update tutem_mall.eb_wechat_user set 
        external_userid = '#{external_userid}',
        nickname = '#{@rds.escape name}',
        headimgurl = '#{@rds.escape avatar}',
        sex = #{gender}
      where unionid = '#{unionid}';"

        usqls += [usql]
        isqls += [isql]
        csqls += [csql]

    end

    commitTrans usqls
    commitTrans isqls
    commitTrans csqls

    puts "done\n"

end

=begin 在此骄傲的保留一个先尝试更新再完全插入的批量方法

  usqls = [] #先尝试更新已有记录
  isqls = [] #再尝试插入不存在就
  csqls = [] #更新其它关联记录的

  puts "recording items"
  items.each do |item|

    print '.'

    now = Time.now
    json = item.to_json
    kkk = item['kkk']       #primary key, 可能是id，也可能是几个键值的联合
    uuu = item['uuu'].to_s  #无需转码的uuid类型
    sss = item['sss'].to_s  #常规字符串类需要转义处理
    iii = item['iii']       #数值

    usql = "update db.tb set 
        uuu = '#{uuu}',
        sss = '#{@rds.escape sss}',
        iii = #{iii},
        json = '#{@rds.escape json},
        update_time = #{now}
      where kkk = #{kkk}"

    isql = "insert into db.tb ( kkk, uuu, sss, iii, json, add_time, update_time ) select
        '#{kkk}',
        '#{uuu}',
        '#{@rds.escape sss},
        #{iii},
        #{now},
        #{now}
      from dual
      where not exists ( select id from db.tb where kkk = '#{kkk}');"

    csql = "update db.cc set 
        uuu = '#{uuu}',
        sss = '#{@rds.escape sss}',
        iii = #{iii},
        json = '#{@rds.escape json},
        update_time = #{now}
      where kkk = #{kkk}"

    usqls += [usql]
    isqls += [isql]
    csqls += [csql]

  end

  commitTrans usqls
  commitTrans isqls
  commitTrans csqls

=end

#使用企业微信外部联系人信息更新用户数据，包括头像和昵称

def record_wework_chat_members

    idx = 1
    chat_id_list = get_wework_group_chat_list
    chat_id_list.each do |chat_id|
        puts "recording group_chat #{chat_id}"
        usqls = [] #用于更新旧记录
        isqls = [] #用于插入新记录
        members = get_wework_group_chat_members chat_id
        members.each do |m|
            json = m.to_json
            userid = m['userid']
            unionid = m['unionid']
            join_time = m['join_time']
            join_scene = m['join_scene']
            group_nickname = m['group_nickname']
            name = m['name']
            x = m['x']
            now = Time.now.to_i
            usql = "update tutem_mall.eb_wework_chat_member
        set unionid = '#{unionid}', join_time = #{join_time}, join_scene = #{join_scene}, group_nickname = '#{@rds.escape group_nickname}',
        name = '#{@rds.escape name}', json = '#{@rds.escape json}', update_time = #{now}
        where chat_id = '#{chat_id}' and userid = '#{userid}';"
            isql = "insert into tutem_mall.eb_wework_chat_member(chat_id, userid, unionid, join_time, join_scene, group_nickname, name, json, add_time, update_time)
        select '#{chat_id}', '#{userid}', '#{unionid}', #{join_time}, #{join_scene}, '#{@rds.escape group_nickname}', 
        '#{@rds.escape name}', '#{@rds.escape json}', #{now}, #{now}
        from dual
        where not exists(select id from tutem_mall.eb_wework_chat_member where chat_id = '#{chat_id}' and userid = '#{userid}');"
            print'.'
            isqls += [isql]
            usqls += [usql]
        end
        #对于已经存在的记录则更新除id,chat_id,userid和add_time之外的内容，这样就可以有效区分出已取关的用户
        commitTrans usqls
        commitTrans isqls
        puts "done\n"
    end

end

def sync_user_profile_by_sql sql

    recs = []
    (queryRds(sql)).each do |q|
        uid = q['uid']
        info = sync_profile_for_uid uid
        recs += [info] if info != {}
    end
    return recs

end

def sync_daily_buyers_info

    t = Time.parse(Date.today.to_s + " 00:00:00").to_i
    sql = "select id, uid from tutem_mall.eb_store_order where paid > 0 and pay_time >= #{t} group by uid"
    recs = sync_user_profile_by_sql sql
    return recs

end

def sync_all_buyers_info

    sql = "select id, uid from tutem_mall.eb_store_order where paid > 0 group by uid"
    recs = sync_user_profile_by_sql sql
    return recs

end

#在eb_wehcat_user中记录过nickname但在eb_user中没有记录昵称的用户
def sync_nickname
    sql = "select uid, nickname from tutem_mall.eb_wechat_user where uid in (select uid from tutem_mall.eb_wechat_user where nickname != '微信用户') and uid in (select uid from tutem_mall.eb_user where nickname = '微信用户')"
    usqls = []
    (queryRds(sql)).each do |q|
        usqls += ["update tutem_mall.eb_user set nickname = '#{@rds.escape q['nickname']}' where uid = #{q['uid']}"]
    end
    commitTrans usqls
end

=begin

dump_hash_to_excel userList, 'user_list'
recs = sync_all_buyers_info
dump_array_to_excel recs, 'all_buyers_info'

=end

record_wework_chat_members
record_wework_external_contacts
sync_nickname

sync_subscription #true
#subscription_summary

recs = sync_daily_buyers_info
dump_array_to_excel recs, 'daily_buyers_info'
